SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TMDOCU_CLIE_Q12]  
/*-------------------------------------------------------*/  
/*---Empresa              : OFISIS S.A.                --*/  
/*---Cliente              : CASSINELLI                 --*/  
/*---Sistema              : Presupuesto                --*/  
/*---M¸dulo               : Empresa                    --*/  
/*---Programa             : Pagos Proyectados por dias --*/  
/*---       y semanas (Ctas x Cobrar)  --*/  
/*---Script               : TMDOCU_CLIE_Q12.sql        --*/  
/*---Nombre SP            : SP_TMDOCU_CLIE_Q12         --*/  
/*---Desarrollado por     : Eduardo Vivar Pomiano      --*/  
/*---Fecha Creaci¸n       : 22/02/2000                 --*/  
/*---Base Datos           : Microsoft Sql Server       --*/  
/*---Versi¸n              : 7.0                        --*/  
/*---Invoca a SP          :                            --*/  
/*-------------------------------------------------------*/  
  
/*--------------------------------------------------------*/  
/*---Modificado 1 por     : Denis Villacr's         --*/  
/*---Fecha Modificaci¸n   : 14/07/2000          --*/   
/*---Detalle Modificaci¸n : Se Agreg© la variable.      --*/  
/*       @ISTI_FECH    --*/  
/*--------------------------------------------------------*/  
  
/*--------------------------------------------------------*/  
/*---Comentarios                                        --*/  
/*---                                                   --*/  
/*---                                                   --*/  
/*--------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/* DROP PROC SP_TMDOCU_CLIE_Q12     */  
/* GRANT ALL ON SP_TMDOCU_CLIE_Q12 TO PUBLIC     */  
/* SP_TMDOCU_CLIE_Q12 '01', '','', '06-01-2000','DIA',''  
  SP_TMDOCU_CLIE_Q12 '99','','SOL','01/01/2000' ,'SEM',''  
 SP_TMDOCU_CLIE_Q12 '', '', '', '02/01/2000','DIA',''  
*/  
  
@ISCO_EMPR  TD_VC_008,  
@ISCO_CLIE  TD_VC_020,  
@ISCO_MONE  TD_VC_003,  
@IDFE_VENC  TD_DT_001,  
@ISTI_OPER  TD_VC_003,  
@ISTI_FECH  TD_VC_001,  
@ISTI_REPO TD_VC_001  
 As  
DECLARE  
@VSCO_EMPR_INIC  TD_VC_002,  
@VSCO_EMPR_FINA  TD_VC_002,  
@VSCO_CLIE_INIC  TD_VC_020,  
@VSCO_CLIE_FINA  TD_VC_020,  
@VSCO_MONE_INIC  TD_VC_003,  
@VSCO_MONE_FINA  TD_VC_003,  
@OSTI_DOCU       TD_VC_003,  
@VSDE_FECH  VarChar(16),  
@VSFE_VENC  TD_VC_010  
  
  
CREATE TABLE #TABLA(CO_EMPR CHAR(2),  
      CO_CLIE VARCHAR(20),   
      CO_MONE CHAR(3),  
       CO_ESTA_DOCU VARCHAR(3),  
      DE_NOMB VARCHAR(100),  
      DE_MONE VARCHAR(50),  
       NO_AUXI VARCHAR(100),  
      CO_TIPO_DOCU VARCHAR(3),  
      NU_DOCU_CLIE VARCHAR(15),  
      IM_0001 NUMERIC(16,4),  
            IM_0002 NUMERIC(16,4),  
      IM_0003 NUMERIC(16,4),  
      IM_0004 NUMERIC(16,4),  
            IM_0005 NUMERIC(16,4),  
      IM_0006 NUMERIC(16,4),  
      IM_0007 NUMERIC(16,4),  
            IM_0008 NUMERIC(16,4),  
      IM_0009 NUMERIC(16,4),  
      IM_0010 NUMERIC(16,4),  
            IM_0011 NUMERIC(16,4),  
      IM_0012 NUMERIC(16,4),  
      IM_0013 NUMERIC(16,4),  
            IM_0014 NUMERIC(16,4),  
      IM_0015 NUMERIC(16,4),  
      IM_SALD NUMERIC(16,4))  
  
  
IF ( @ISCO_EMPR = '' OR LEN(@ISCO_EMPR) = 0 OR @ISCO_EMPR IS NULL )  
 SELECT @VSCO_EMPR_INIC = MIN(CO_EMPR),   
 @VSCO_EMPR_FINA = MAX(CO_EMPR)  
 FROM   TMEMPR  
ELSE  
 BEGIN  
  SELECT @VSCO_EMPR_INIC = @ISCO_EMPR  
  SELECT @VSCO_EMPR_FINA = @ISCO_EMPR  
 END  
  
  
IF ( @ISCO_MONE = '' OR LEN(@ISCO_MONE) = 0 OR @ISCO_MONE IS NULL )  
 SELECT @VSCO_MONE_INIC = MIN(CO_MONE), @VSCO_MONE_FINA = MAX(CO_MONE)  
 FROM  TTMONE  
ELSE  
 BEGIN  
  SELECT @VSCO_MONE_INIC = @ISCO_MONE  
  SELECT @VSCO_MONE_FINA = @ISCO_MONE  
 END  
  
  
IF ( @ISCO_CLIE = '' OR LEN(@ISCO_CLIE) = 0 OR @ISCO_CLIE IS NULL )  
 SELECT  @VSCO_CLIE_INIC = MIN(CO_AUXI_EMPR),   
  @VSCO_CLIE_FINA = MAX(CO_AUXI_EMPR)  
 FROM    TMAUXI_EMPR  
 Where   CO_EMPR >=  @VSCO_EMPR_INIC   
  And CO_EMPR  <= @VSCO_EMPR_FINA  
   And TI_AUXI_EMPR = 'C'  
ELSE  
 BEGIN  
    SELECT @VSCO_CLIE_INIC = @ISCO_CLIE  
    SELECT @VSCO_CLIE_FINA = @ISCO_CLIE  
 END  
  
  
/* SELECCION POR FECHA DE VENCIMIENTO */  
  
IF @ISTI_FECH = 'V'   
SELECT @VSDE_FECH = 'C1.FE_VENC'  
  
IF @ISTI_FECH = 'P'   
SELECT @VSDE_FECH = 'C1.FE_PROG_COBR'  
  
SELECT @VSFE_VENC = Convert(VarChar(10),@IDFE_VENC,103)  
  
  
EXEC SP_TTDOCU_CNTB_Q02 6,@OSTI_DOCU    
  
IF @ISTI_REPO='D'  
BEGIN  
IF @ISTI_OPER = 'DIA'  
 BEGIN  
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_CLIE, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI, CO_TIPO_DOCU, NU_DOCU_CLIE,  
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,   
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,  
       IM_0013, IM_0014, IM_0015 )  
 SELECT   
  C1.CO_EMPR,  
  C1.CO_MONE,  
  C1.CO_CLIE,  
  C1.CO_ESTA_DOCU,  
  T1.DE_NOMB,   T2.DE_MONE,  
  T3.NO_AUXI,  
 C1.CO_TIPO_DOCU,  
 C1.NU_DOCU_CLIE,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "0")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "1")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "2")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "3")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "4")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "5")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "6")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "7")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "8")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "9")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "10")*( ISNULL( IM_TOTA, 0) -ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "11")*( ISNULL( IM_TOTA, 0) -ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "12")*( ISNULL( IM_TOTA, 0) -ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "13")*( ISNULL( IM_TOTA, 0) -ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "14")*( ISNULL( IM_TOTA, 0) -ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))   
 from    TMDOCU_CLIE  C1,TMEMPR  T1 , TTMONE T2, TMAUXI_EMPR T3, TTDOCU_CNTB T5  
 where   C1.CO_ESTA_DOCU =  "ACT"  
   And   '+@VSDE_FECH+'  >= Convert(DateTime,"'+@VSFE_VENC+'",103)  
   And   '+@VSDE_FECH+'  < Dateadd(day,16,Convert(DateTime,"'+@VSFE_VENC+'",103))  
   And  C1.CO_EMPR   >= "'+@VSCO_EMPR_INIC+'"  
   And  C1.CO_EMPR   <= "'+@VSCO_EMPR_FINA+'"  
   And  C1.CO_MONE   >= "'+@VSCO_MONE_INIC+'"  
   And  C1.CO_MONE   <= "'+@VSCO_MONE_FINA+'"  
   and  C1.CO_CLIE  >= "'+@VSCO_CLIE_INIC+'"  
   and  C1.CO_CLIE  <= "'+@VSCO_CLIE_FINA+'"  
   and  T1.CO_EMPR    =  C1.CO_EMPR  
   and  C1.CO_MONE   =  T2.CO_MONE  
   and  C1.CO_EMPR   =  T3.CO_EMPR  
   and     T3.TI_AUXI_EMPR = "C"  
   and  T3.CO_AUXI_EMPR   =  C1.CO_CLIE  
  
   AND  C1.CO_TIPO_DOCU = T5.TI_DOCU  
    
  
  
 UNION   
  
 SELECT  
  D1.CO_EMPR ,  
 D1.CO_MONE ,  
  D1.CO_CLIE ,  
  D1.CO_ESTA_DOCU,  
  T1.DE_NOMB,  
  T2.DE_MONE,  
  T3.NO_AUXI,  
              "'+'LET'+'",  
 D1.NU_LETR_CLIE,  
    PATINDEX( CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "0")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "1")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "2")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "3")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "4")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "5")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "6")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "7")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "8")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "9")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "10")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "11")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "12")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "13")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "14")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )  
 from  TMLETR_CLIE  D1,TMEMPR  T1 , TTMONE T2 , TMAUXI_EMPR T3  
 where  D1.CO_ESTA_DOCU =  "ACE"  
   And  D1.FE_VENC >= Convert(DateTime,"'+@VSFE_VENC+'",103)  
   And  D1.FE_VENC <  Dateadd(Day,16,Convert(DateTime,"'+@VSFE_VENC+'",103))  
 and D1.CO_EMPR  >= "'+@VSCO_EMPR_INIC+'"  
 and D1.CO_EMPR  <= "'+@VSCO_EMPR_FINA+'"  
 and D1.CO_MONE  >= "'+@VSCO_MONE_INIC+'"   
 and D1.CO_MONE  <= "'+@VSCO_MONE_FINA+'"  
 and D1.CO_CLIE >= "'+@VSCO_CLIE_INIC+'"  
 and D1.CO_CLIE <= "'+@VSCO_CLIE_FINA+'"  
 and T1.CO_EMPR  =  D1.CO_EMPR  
 and D1.CO_MONE =  T2.CO_MONE  
 and D1.CO_EMPR =  T3.CO_EMPR  
 and    T3.TI_AUXI_EMPR = "C"  
 and T3.CO_AUXI_EMPR =  D1.CO_CLIE  
    
 ORDER BY  1 , 2, 3')  
   
  
END   
  
/*OPCION DE SEMANAS*/  
  
IF @ISTI_OPER = 'SEM'  
BEGIN   
  
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_CLIE, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI, CO_TIPO_DOCU, NU_DOCU_CLIE,  
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,   
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,  
       IM_0013, IM_0014, IM_0015 )  
 SELECT    
 C1.CO_EMPR ,  
 C1.CO_MONE ,  
 C1.CO_CLIE ,  
 C1.CO_ESTA_DOCU,  
 T1.DE_NOMB,  
 T2.DE_MONE,  
 T3.NO_AUXI ,  
C1.CO_TIPO_DOCU,  
C1.NU_DOCU_CLIE,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "0")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "1")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "2")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "3")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "4")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "5")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "6")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "7")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "8")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "9")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "10")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "11")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "12")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "13")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ,  
   PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "14")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))   
 From  TMDOCU_CLIE  C1,TMEMPR  T1 , TTMONE T2 , TMAUXI_EMPR T3 ,  TTDOCU_CNTB T5  
 where  C1.CO_ESTA_DOCU =  "ACT"  
   And  '+@VSDE_FECH+' >= Convert(DateTime,"'+@VSFE_VENC+'",103)  
   And  '+@VSDE_FECH+' < dateadd(day,119,Convert(DateTime,"'+@VSFE_VENC+'",103))  
 and C1.CO_EMPR  >= "'+@VSCO_EMPR_INIC+'"  
 and C1.CO_EMPR  <= "'+@VSCO_EMPR_FINA+'"  
 and C1.CO_MONE  >= "'+@VSCO_MONE_INIC+'"  
 and C1.CO_MONE  <= "'+@VSCO_MONE_FINA+'"  
 and C1.CO_CLIE >= "'+@VSCO_CLIE_INIC+'"  
 and C1.CO_CLIE <= "'+@VSCO_CLIE_FINA+'"  
 and T1.CO_EMPR  =  C1.CO_EMPR  
 and C1.CO_MONE =  T2.CO_MONE  
 and C1.CO_EMPR =  T3.CO_EMPR  
 and    T3.TI_AUXI_EMPR = "C"  
 and T3.CO_AUXI_EMPR =  C1.CO_CLIE  
  
 AND  C1.CO_TIPO_DOCU = T5.TI_DOCU  
  
  
UNION   
 SELECT     
 D1.CO_EMPR ,  
 D1.CO_MONE ,  
 D1.CO_CLIE ,  
 D1.CO_ESTA_DOCU,  
 T1.DE_NOMB,  
 T2.DE_MONE,  
 T3.NO_AUXI,   
"'+'LET'+'",  
D1.NU_LETR_CLIE,  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "0")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "1")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "2")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "3")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "4")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "5")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "6")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "7")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "8")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "9")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "10")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "11")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "12")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "13")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "14")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )  
 From TMLETR_CLIE  D1,TMEMPR  T1 , TTMONE T2 , TMAUXI_EMPR T3  
 where  D1.CO_ESTA_DOCU =  "ACE"  
 and  D1.FE_VENC >= Convert(DateTime,"'+@VSFE_VENC+'",103)    
 and  D1.FE_VENC <  dateadd(day,119,Convert(DateTime,"'+@VSFE_VENC+'",103))  
 and D1.CO_EMPR  >= "'+@VSCO_EMPR_INIC+'"  
 and D1.CO_EMPR  <= "'+@VSCO_EMPR_FINA+'"  
 and D1.CO_MONE  >= "'+@VSCO_MONE_INIC+'"  
 and D1.CO_MONE  <= "'+@VSCO_MONE_FINA+'"  
 and D1.CO_CLIE >= "'+@VSCO_CLIE_INIC+'"  
 and D1.CO_CLIE <= "'+@VSCO_CLIE_FINA+'"  
 and T1.CO_EMPR  =  D1.CO_EMPR  
 and D1.CO_MONE =  T2.CO_MONE  
 and D1.CO_EMPR =  T3.CO_EMPR  
 and    T3.TI_AUXI_EMPR = "C"  
 and T3.CO_AUXI_EMPR =  D1.CO_CLIE  
  
ORDER BY 1, 2, 3')   
  
END  
END  
  
ELSE  
BEGIN  
IF @ISTI_OPER = 'DIA'  
 BEGIN  
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_CLIE, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI,  
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,   
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,  
       IM_0013, IM_0014, IM_0015 )  
 SELECT   
  C1.CO_EMPR,  
  C1.CO_MONE,  
  
  C1.CO_CLIE,  
  
  MAX(C1.CO_ESTA_DOCU),  
  MAX(T1.DE_NOMB),   MAX(T2.DE_MONE),  
  MAX(T3.NO_AUXI),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "0")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "1")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "2")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "3")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "4")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "5")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "6")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "7")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "8")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "9")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "10")*( ISNULL( IM_TOTA, 0) -ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "11")*( ISNULL( IM_TOTA, 0) -ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "12")*( ISNULL( IM_TOTA, 0) -ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  

  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "13")*( ISNULL( IM_TOTA, 0) -ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "14")*( ISNULL( IM_TOTA, 0) -ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) )  
 from    TMDOCU_CLIE  C1,TMEMPR  T1 , TTMONE T2, TMAUXI_EMPR T3, TTDOCU_CNTB T5  
 where   C1.CO_ESTA_DOCU =  "ACT"  
   And   '+@VSDE_FECH+'  >= Convert(DateTime,"'+@VSFE_VENC+'",103)  
   And   '+@VSDE_FECH+'  < Dateadd(day,16,Convert(DateTime,"'+@VSFE_VENC+'",103))  
   And  C1.CO_EMPR   >= "'+@VSCO_EMPR_INIC+'"  
   And  C1.CO_EMPR   <= "'+@VSCO_EMPR_FINA+'"  
   And  C1.CO_MONE   >= "'+@VSCO_MONE_INIC+'"  
   And  C1.CO_MONE   <= "'+@VSCO_MONE_FINA+'"  
   and  C1.CO_CLIE  >= "'+@VSCO_CLIE_INIC+'"  
   and  C1.CO_CLIE  <= "'+@VSCO_CLIE_FINA+'"  
   and  T1.CO_EMPR    =  C1.CO_EMPR  
   and  C1.CO_MONE   =  T2.CO_MONE  
   and  C1.CO_EMPR   =  T3.CO_EMPR  
   and     T3.TI_AUXI_EMPR = "C"  
   and  T3.CO_AUXI_EMPR   =  C1.CO_CLIE  
  
   AND  C1.CO_TIPO_DOCU = T5.TI_DOCU  
   
 GROUP BY C1.CO_EMPR, C1.CO_MONE, C1.CO_CLIE   
  
  
 UNION   
  
 SELECT  
  D1.CO_EMPR ,  
 D1.CO_MONE ,  
  D1.CO_CLIE ,  
  MAX(D1.CO_ESTA_DOCU),  
  MAX(T1.DE_NOMB),  
  MAX(T2.DE_MONE),  
  MAX(T3.NO_AUXI),  
   SUM( PATINDEX( CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "0")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "1")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "2")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "3")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "4")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "5")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "6")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "7")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "8")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "9")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "10")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "11")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "12")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "13")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "14")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ))  
 from  TMLETR_CLIE  D1,TMEMPR  T1 , TTMONE T2 , TMAUXI_EMPR T3  
 where  D1.CO_ESTA_DOCU =  "ACE"  
   And  D1.FE_VENC >= Convert(DateTime,"'+@VSFE_VENC+'",103)  
   And  D1.FE_VENC <  Dateadd(Day,16,Convert(DateTime,"'+@VSFE_VENC+'",103))  
 and D1.CO_EMPR  >= "'+@VSCO_EMPR_INIC+'"  
 and D1.CO_EMPR  <= "'+@VSCO_EMPR_FINA+'"  
 and D1.CO_MONE  >= "'+@VSCO_MONE_INIC+'"   
 and D1.CO_MONE  <= "'+@VSCO_MONE_FINA+'"  
 and D1.CO_CLIE >= "'+@VSCO_CLIE_INIC+'"  
 and D1.CO_CLIE <= "'+@VSCO_CLIE_FINA+'"  
 and T1.CO_EMPR  =  D1.CO_EMPR  
 and D1.CO_MONE =  T2.CO_MONE  
 and D1.CO_EMPR =  T3.CO_EMPR  
 and    T3.TI_AUXI_EMPR = "C"  
 and T3.CO_AUXI_EMPR =  D1.CO_CLIE  
 GROUP BY D1.CO_EMPR, D1.CO_MONE, D1.CO_CLIE   
 ORDER BY  1 , 2, 3')  
  
END   
  
/*OPCION DE SEMANAS*/  
  
IF @ISTI_OPER = 'SEM'  
BEGIN   
  
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_CLIE, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI,  
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,   
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,  
       IM_0013, IM_0014, IM_0015 )  
 SELECT    
 C1.CO_EMPR ,  
 C1.CO_MONE ,  
 C1.CO_CLIE ,  
 MAX(C1.CO_ESTA_DOCU),  
 MAX(T1.DE_NOMB),  
 MAX(T2.DE_MONE),  
 MAX(T3.NO_AUXI) ,  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "0")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "1")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "2")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "3")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "4")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "5")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "6")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "7")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "8")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "9")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "10")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "11")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "12")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "13")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "14")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) )  
 From  TMDOCU_CLIE  C1,TMEMPR  T1 , TTMONE T2 , TMAUXI_EMPR T3 ,  TTDOCU_CNTB T5  
 where  C1.CO_ESTA_DOCU =  "ACT"  
   And  '+@VSDE_FECH+' >= Convert(DateTime,"'+@VSFE_VENC+'",103)  
   And  '+@VSDE_FECH+' < dateadd(day,119,Convert(DateTime,"'+@VSFE_VENC+'",103))  
 and C1.CO_EMPR  >= "'+@VSCO_EMPR_INIC+'"  
 and C1.CO_EMPR  <= "'+@VSCO_EMPR_FINA+'"  
 and C1.CO_MONE  >= "'+@VSCO_MONE_INIC+'"  
 and C1.CO_MONE  <= "'+@VSCO_MONE_FINA+'"  
 and C1.CO_CLIE >= "'+@VSCO_CLIE_INIC+'"  
 and C1.CO_CLIE <= "'+@VSCO_CLIE_FINA+'"  
 and T1.CO_EMPR  =  C1.CO_EMPR  
 and C1.CO_MONE =  T2.CO_MONE  
 and C1.CO_EMPR =  T3.CO_EMPR  
 and    T3.TI_AUXI_EMPR = "C"  
 and T3.CO_AUXI_EMPR =  C1.CO_CLIE  
  
 AND  C1.CO_TIPO_DOCU = T5.TI_DOCU  
  
  
  
GROUP BY C1.CO_EMPR, C1.CO_MONE, C1.CO_CLIE  
  
UNION   
 SELECT     
 D1.CO_EMPR ,  
 D1.CO_MONE ,  
 D1.CO_CLIE ,  
 MAX(D1.CO_ESTA_DOCU),  
 MAX(T1.DE_NOMB),  
 MAX(T2.DE_MONE),  
 MAX(T3.NO_AUXI),   
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "0")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "1")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "2")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "3")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "4")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "5")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "6")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "7")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "8")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "9")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "10")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "11")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "12")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "13")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) )),  
 SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), D1.FE_VENC)), "14")*( ISNULL( IM_TOTA, 0) - ISNULL( IM_PAGA,0) ))  
 From TMLETR_CLIE  D1,TMEMPR  T1 , TTMONE T2 , TMAUXI_EMPR T3  
 where  D1.CO_ESTA_DOCU =  "ACE"  
 and  D1.FE_VENC >= Convert(DateTime,"'+@VSFE_VENC+'",103)    
 and  D1.FE_VENC <  dateadd(day,119,Convert(DateTime,"'+@VSFE_VENC+'",103))  
 and D1.CO_EMPR  >= "'+@VSCO_EMPR_INIC+'"  
 and D1.CO_EMPR  <= "'+@VSCO_EMPR_FINA+'"  
 and D1.CO_MONE  >= "'+@VSCO_MONE_INIC+'"  
 and D1.CO_MONE  <= "'+@VSCO_MONE_FINA+'"  
 and D1.CO_CLIE >= "'+@VSCO_CLIE_INIC+'"  
 and D1.CO_CLIE <= "'+@VSCO_CLIE_FINA+'"  
 and T1.CO_EMPR  =  D1.CO_EMPR  
 and D1.CO_MONE =  T2.CO_MONE  
 and D1.CO_EMPR =  T3.CO_EMPR  
 and    T3.TI_AUXI_EMPR = "C"  
 and T3.CO_AUXI_EMPR =  D1.CO_CLIE  
GROUP BY D1.CO_EMPR, D1.CO_MONE, D1.CO_CLIE  
ORDER BY 1, 2, 3')   
  
END  
END  
  
  
EXECUTE (' Insert into #TABLA (CO_EMPR,  CO_CLIE , CO_MONE ,    CO_ESTA_DOCU ,  
      DE_NOMB , DE_MONE , IM_0001 ,IM_0002 ,  
      IM_0003 , IM_0004 , IM_0005 ,  
      IM_0006 , IM_0007 , IM_0008 ,  
      IM_0009 , IM_0010 , IM_0011 ,  
      IM_0012 , IM_0013 , IM_0014 ,  
      IM_0015 , IM_SALD)  
Select T1.CO_EMPR, T1.CO_CLIE, T1.CO_MONE, NULL, NULL, NULL, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, Sum(T1.IM_TOTA-T1.IM_PAGA)  
From TMDOCU_CLIE T1  
Where  T1.CO_EMPR  >= "'+@VSCO_EMPR_INIC+'"  
And T1.CO_EMPR  <= "'+@VSCO_EMPR_FINA+'"  
and T1.CO_CLIE >= "'+@VSCO_CLIE_INIC+'"  
and T1.CO_CLIE <= "'+@VSCO_CLIE_FINA+'"  
and T1.CO_MONE  >= "'+@VSCO_MONE_INIC+'"   
and T1.CO_MONE  <= "'+@VSCO_MONE_FINA+'"  
And  FE_VENC <  Convert(DateTime,"'+@VSFE_VENC+'",103)  
and     T1.CO_ESTA_DOCU =  "ACT"  
Group By     T1.CO_EMPR, T1.CO_MONE, T1.CO_CLIE')  
  
  
IF @ISTI_REPO='D'  
BEGIN  
  
SELECT CO_EMPR, CO_MONE, CO_CLIE, MAX(CO_ESTA_DOCU), MAX(DE_NOMB),MAX(DE_MONE),  
  MAX(NO_AUXI),  
              SUM(ISNULL(IM_0001, 0) ), SUM(ISNULL(IM_0002, 0) ),  
 SUM(ISNULL(IM_0003, 0) ), SUM(ISNULL(IM_0004, 0) ),  
 SUM(ISNULL(IM_0005, 0) ), SUM(ISNULL(IM_0006, 0) ),  
 SUM(ISNULL(IM_0007, 0) ), SUM(ISNULL(IM_0008, 0) ),  
 SUM(ISNULL(IM_0009, 0) ), SUM(ISNULL(IM_0010, 0) ),  
 SUM(ISNULL(IM_0011, 0) ), SUM(ISNULL(IM_0012, 0) ),  
 SUM(ISNULL(IM_0013, 0) ), SUM(ISNULL(IM_0014, 0) ),  
 SUM(ISNULL(IM_0015, 0) ), SUM(ISNULL(IM_SALD, 0) ),  CO_TIPO_DOCU, NU_DOCU_CLIE  
FROM #TABLA  
GROUP BY CO_EMPR , CO_MONE, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE  
HAVING (  SUM(ISNULL( IM_0001, 0) )<>0  OR    SUM(ISNULL( IM_0002, 0) )<>0   OR  
                 SUM(ISNULL( IM_0003, 0) )<>0   OR    SUM(ISNULL( IM_0004, 0) )<>0   OR  
     SUM(ISNULL( IM_0005, 0) )<>0   OR    SUM(ISNULL( IM_0006, 0) )<>0   OR  
     SUM(ISNULL( IM_0007, 0) )<>0   OR    SUM(ISNULL( IM_0008, 0) )<>0   OR  
     SUM(ISNULL( IM_0009, 0) )<>0   OR    SUM(ISNULL( IM_0010, 0) )<>0   OR  
     SUM(ISNULL( IM_0011, 0) )<>0   OR    SUM(ISNULL( IM_0012, 0) )<>0   OR  
     SUM(ISNULL( IM_0013, 0) )<>0   OR    SUM(ISNULL( IM_0014, 0) )<>0   OR  
     SUM(ISNULL( IM_0015, 0) )<>0  )  
ORDER BY CO_EMPR , CO_MONE, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE  
  
END   
  
/*      */  
ELSE  
BEGIN  
SELECT CO_EMPR, CO_MONE, CO_CLIE, MAX(CO_ESTA_DOCU), MAX(DE_NOMB),MAX(DE_MONE),  
  MAX(NO_AUXI), SUM(ISNULL(IM_0001, 0) ), SUM(ISNULL(IM_0002, 0) ),  
 SUM(ISNULL(IM_0003, 0) ), SUM(ISNULL(IM_0004, 0) ),  
 SUM(ISNULL(IM_0005, 0) ), SUM(ISNULL(IM_0006, 0) ),  
 SUM(ISNULL(IM_0007, 0) ), SUM(ISNULL(IM_0008, 0) ),  
 SUM(ISNULL(IM_0009, 0) ), SUM(ISNULL(IM_0010, 0) ),  
 SUM(ISNULL(IM_0011, 0) ), SUM(ISNULL(IM_0012, 0) ),  
 SUM(ISNULL(IM_0013, 0) ), SUM(ISNULL(IM_0014, 0) ),  
 SUM(ISNULL(IM_0015, 0) ), SUM(ISNULL(IM_SALD, 0) ), NULL, NULL  
FROM #TABLA  
GROUP BY CO_EMPR , CO_MONE, CO_CLIE  
HAVING (  SUM(ISNULL( IM_0001, 0) )<>0  OR    SUM(ISNULL( IM_0002, 0) )<>0   OR  
                 SUM(ISNULL( IM_0003, 0) )<>0   OR    SUM(ISNULL( IM_0004, 0) )<>0   OR  
     SUM(ISNULL( IM_0005, 0) )<>0   OR    SUM(ISNULL( IM_0006, 0) )<>0   OR  
     SUM(ISNULL( IM_0007, 0) )<>0   OR    SUM(ISNULL( IM_0008, 0) )<>0   OR  
     SUM(ISNULL( IM_0009, 0) )<>0   OR    SUM(ISNULL( IM_0010, 0) )<>0   OR  
     SUM(ISNULL( IM_0011, 0) )<>0   OR    SUM(ISNULL( IM_0012, 0) )<>0   OR  
     SUM(ISNULL( IM_0013, 0) )<>0   OR    SUM(ISNULL( IM_0014, 0) )<>0   OR  
     SUM(ISNULL( IM_0015, 0) )<>0  )  
ORDER BY CO_EMPR , CO_MONE, CO_CLIE  
  
END  
/*------------------------- FIN ------------------------------ */


GO
